package com.oristand.printer.address.util;

import java.sql.*;

/**
 * sqlite 工具类
 *
 * @author chenkailiang
 * @date 2020/9/3
 */
public class SqliteUtil {

    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    private String dbFilePath;

    /**
     *  构造函数
     * @param dbFilePath  db文件路径
     * @throws ClassNotFoundException ClassNotFoundException
     * @throws SQLException SQLException
     */
    public SqliteUtil(String dbFilePath) throws ClassNotFoundException, SQLException {
        this.dbFilePath = dbFilePath;
        connection = getConnection(dbFilePath);
    }

    /**
     * 获取数据库连接
     * @param dbFilePath  db文件路径
     * @return connection
     * @throws ClassNotFoundException ClassNotFoundException
     * @throws SQLException SQLException
     */
    public Connection getConnection(String dbFilePath) throws ClassNotFoundException, SQLException {
        Class.forName("org.sqlite.JDBC");
        return DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);
    }

    /**
     * 获取数据库连接
     * @param dbFilePath  db文件路径
     * @return connection
     * @throws ClassNotFoundException ClassNotFoundException
     * @throws SQLException SQLException
     */
    public static  Connection getConnectionByFilePath(String dbFilePath) throws ClassNotFoundException, SQLException {
        Class.forName("org.sqlite.JDBC");
        return DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);
    }



    /**
     * 执行sql查询
     * @param sql sql
     * @param rse resultSet
     * @param <T> T
     * @return T
     * @throws SQLException SQLException
     * @throws ClassNotFoundException ClassNotFoundException
     */
    public <T> T executeQuery(String sql, ResultSetExtractor<T> rse) throws SQLException, ClassNotFoundException {
        try {
            resultSet = getStatement().executeQuery(sql);
            return  rse.extractData(resultSet);
        } finally {
            destroyed();
        }
    }

    /**
     * 查询数量
     * @param sql sql
     * @return 数量
     * @throws SQLException SQLException
     * @throws ClassNotFoundException ClassNotFoundException
     */
    public int executeQuery(String sql)throws SQLException, ClassNotFoundException{
        try {
            resultSet = getStatement().executeQuery(sql);
            int count = 0;
            if(resultSet.next()){
                count = resultSet.getInt(1);
            }
            return count;
        } finally {
            destroyed();
        }
    }


    /**
     * 执行数据库更新sql语句
     * @param sql sql
     * @throws SQLException SQLException
     * @throws ClassNotFoundException ClassNotFoundException
     */
    public void executeUpdate(String sql) throws SQLException, ClassNotFoundException {
        try {
            getStatement().executeUpdate(sql);
        } finally {
            destroyed();
        }

    }

    /**
     * 获取连接
     * @return Connection
     * @throws ClassNotFoundException ClassNotFoundException
     * @throws SQLException SQLException
     */
    private Connection getConnection() throws ClassNotFoundException, SQLException {
        if (null == connection) {
            connection = getConnection(dbFilePath);
        }
        return connection;
    }

    /**
     * 获取Statement
     * @return Statement
     * @throws SQLException SQLException
     * @throws ClassNotFoundException ClassNotFoundException
     */
    private Statement getStatement() throws SQLException, ClassNotFoundException {
        if (null == statement) {
            statement = getConnection().createStatement();
        }
        return statement;
    }

    /**
     * 数据库资源关闭和释放
     */
    private void destroyed() {
        try {
            if (null != resultSet) {
                resultSet.close();
                resultSet = null;
            }
            if (null != statement) {
                statement.close();
                statement = null;
            }
            if (null != connection) {
                connection.close();
                connection = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}
